const http = require('http');
const fs = require('fs');
const qs = require('querystring');
const mysql = require('mysql');
const url = require('url');
const express = require('express');
const cookieParser = require('cookie-parser');
const session = require('express-session');

const root_path = process.cwd() + '/src';

const conn = mysql.createConnection({
    // user: 数据库用户名,
    // password: 用户名对应密码,
    // host: 数据库服务器所在地址，本机填‘localhost’,
    // port: 数据库服务端口，默认3306，可忽略不填
    // database: 数据库名
    user: '',
    password: '',
    host: '',
    database: '',
});

conn.connect(err => {
    if (!err) {
        console.log('数据库连接成功！');
    } else {
        console.log('数据库连接失败!');
    }
});

const app = express();

app.use(cookieParser('sessionshop'));
app.use(session({
    secret: 'sessionshop',
    resave: true,
    saveUninitialized: true
}));

app.use(function (req, res, next) {
    if (req.session.user) {
        next();
    } else {
        var allowed_pass = false;
        var allowed_url = new Array([
            new RegExp('^/login-confirm$'), new RegExp('^/register-confirm$'), new RegExp('/reset-user-confirm'),
            new RegExp('^/page/login.html$'), new RegExp('^/page/register.html$'),
            new RegExp('^/page/reset-user.html$'), new RegExp('^/page/guest-main.html$'), new RegExp('^/$'),
            new RegExp('^[\\w\\-\\/\\.]*\.js$'), new RegExp('^[\\w\\-\\/\\.]*\.css$'), new RegExp('^[\\w\\-\\/\\.]*\.ico$'),
            new RegExp('^/company-info$'), new RegExp('^/get-shop-classfier$'), new RegExp('^/products-list-onshop'),
            new RegExp('^/static/')
        ]);
        for (var i = 0; i < allowed_url[0].length; i += 1) {
            if (req.url.search(allowed_url[0][i]) != -1) {
                // console.log(allowed_url[0][i]);
                allowed_pass = true;
            }
        }
        // console.log(req.url, '--', allowed_pass ? '放行': '不允放行');
        if (allowed_pass) next();
        else res.redirect('/page/login.html');
    }
    return;
});

app.use(express.static('./src')); // 挂载静态资源

app.get('/', (req, res) => {
    res.sendFile('/page/guest-main.html', { root: root_path });
    return;
});

// 登陆验证
app.post('/login-confirm', (req, res) => {
    var postData = '';
    req.on('data', function (chuck) {
        postData += chuck;
    }).on('end', function () {
        var postObj = qs.parse(postData);
        var username = postObj['username'];
        var password = postObj['password'];
        var sql_string = `select * from user where name='${username}' and password='${password}';`;
        // console.log(sql_string);
        conn.query(sql_string, (err, results) => {
            var res_json = undefined;
            try {
                res_json = JSON.stringify({ result_permission: results[0].permission });
                req.session.user = { id: results[0].id, name: results[0].name, permission: results[0].permission };
            } catch (err) {
                res_json = JSON.stringify({ result_permission: -1 });
            } finally {
                res.writeHead(200, { "Content-Type": "application/json" });
                res.write(res_json);
                res.end();
            }
        });
    });
    return;
});

// 退出登陆
app.get('/logout', (req, res) => {
    req.session.user = null;
    res.redirect('/');
});

// 注册验证
app.post('/register-confirm', (req, res) => {
    var postData = '';
    req.on('data', function (chuck) {
        postData += chuck;
    }).on('end', function () {
        var postObj = qs.parse(postData);
        var username = postObj['username'];
        var password = postObj['password'];
        var sql_string = `insert into user value(NULL, '${username}', '${password}', 1);`;
        conn.query(sql_string, (err, results) => {
            var res_json = undefined;
            if (err) {
                res_json = JSON.stringify({ result: 0 });
                res.writeHead(200, { "Content-Type": "application/json" });
                res.write(res_json);
                res.end();
            } else {
                res_json = JSON.stringify({ result: 1 });
                res.writeHead(200, { "Content-Type": "application/json" });
                res.write(res_json);
                res.end();
            }
        });
    })
});

// 重置密码
app.post('/reset-user-confirm', (req, res) => {
    var postData = '';
    req.on('data', function (chuck) {
        postData += chuck;
    }).on('end', function () {
        var postObj = qs.parse(postData);
        var username = postObj['username'];
        var password = postObj['password'];
        var sql_string = `update user set password='${password}' where name='${username}';`;
        conn.query(sql_string, (err, results) => {
            var res_json = undefined;
            if (err) {
                res_json = JSON.stringify({ result: 0 });
                res.writeHead(200, { "Content-Type": "application/json" });
                res.write(res_json);
                res.end();
            } else {
                res_json = JSON.stringify({ result: 1 });
                res.writeHead(200, { "Content-Type": "application/json" });
                res.write(res_json);
                res.end();
            }
        });
    })
});

app.get('/get-show-user-name', (req, res) => {
    var username = req.session.user['name'];
    res.writeHead(200, { "Content-Type": "application/json" });
    res.write(JSON.stringify({ status: true, result: username }));
    res.end();
});

// 获取员工列表
app.get('/parters-list', (req, res) => {
    var sql_string = `select * from user t1 inner join parters t2 on t1.id = t2.user_id where permission != 1 and permission != 2;`;
    conn.query(sql_string, (err, results) => {
        var res_json = undefined;
        try {
            res_json = JSON.stringify({ status: true, result: results });
        } catch (err) {
            res_json = JSON.stringify({ status: false });
        } finally {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(res_json);
            res.end();
        }
    })
});

// 添加员工
app.post('/admin-add-parter', (req, res) => {
    var postData = '';
    req.on('data', function (chuck) {
        postData += chuck;
    }).on('end', function () {
        var postObj = qs.parse(postData);
        var name = postObj['name'];
        var tname = postObj['tname'];
        var gender = postObj['gender'];
        // var parter_number = postObj['parter-number'];
        var job = postObj['job'];

        var sql_string = `insert into user value(NULL, '${name}', '123456', ${job});`;
        conn.query(sql_string, (err, results) => {
            if (err) {
                console.log(1, err);
                res.writeHead(200, { "Content-Type": "application/json" });
                res.write(JSON.stringify({ status: false }));
                res.end();
            } else {
                var sql_string = `select id from user where name='${name}'`;
                conn.query(sql_string, (err, results) => {
                    if (err) {
                        console.log(2, err);
                        res.writeHead(200, { "Content-Type": "application/json" });
                        res.write(JSON.stringify({ status: false }));
                        res.end();
                    } else {
                        var id = results[0]['id'];
                        var sql_string = `insert into parters value(NULL, ${id}, ${gender}, '${tname}');`;
                        conn.query(sql_string, (err, results) => {
                            if (err) {
                                console.log(3, err);
                                res.writeHead(200, { "Content-Type": "application/json" });
                                res.write(JSON.stringify({ status: false }));
                                res.end();
                            } else {
                                res.writeHead(200, { "Content-Type": "application/json" });
                                res.write(JSON.stringify({ status: true }));
                                res.end();
                            }
                        });
                    }
                });
            }
        })
    });
});

// 删除员工
app.get('/admin-delete-parter', (req, res) => {
    var id = req.query['id'];
    var sql_string = `delete from user where id=${id};`;
    conn.query(sql_string, (err, results) => {
        if (err) {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(JSON.stringify({ status: false }));
            res.end();
        } else {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(JSON.stringify({ status: true }));
            res.end();
        }
    });
});

// 编辑员工
app.post('/admin-edit-parter', (req, res) => {
    var postData = '';
    var id = req.query['id'];
    req.on('data', function (chuck) {
        postData += chuck;
    }).on('end', function () {
        postObj = qs.parse(postData);
        var name = postObj['name'];
        var tname = postObj['tname'];
        var gender = postObj['gender'];
        // var parter_number = postObj['parter-number'];
        var job = postObj['job'];
        var sql_string = `update user set name='${name}', permission=${job} where id=${id}`;
        conn.query(sql_string, (err, results) => {
            if (err) {
                console.log(1, err);
                res.writeHead(200, { "Content-Type": "application/json" });
                res.write(JSON.stringify({ status: false }));
                res.end();
            } else {
                var sql_string = `update parters set tname='${tname}', gender=${gender} where user_id=${id};`;
                console.log(sql_string);
                conn.query(sql_string, (err, results) => {
                    if (err) {
                        console.log(2, err);
                        res.writeHead(200, { "Content-Type": "application/json" });
                        res.write(JSON.stringify({ status: false }));
                        res.end();
                    } else {
                        res.writeHead(200, { "Content-Type": "application/json" });
                        res.write(JSON.stringify({ status: true }));
                        res.end();
                    }
                });
            }
        })
    });
});

// 用户列表
app.get('/users-list', (req, res) => {
    var sql_string = `select * from user where permission = 1;`;
    conn.query(sql_string, (err, results) => {
        var res_json = undefined;
        try {
            res_json = JSON.stringify({ status: true, result: results });
        } catch (err) {
            res_json = JSON.stringify({ status: false });
        } finally {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(res_json);
            res.end();
        }
    });
});

// 添加用户
app.post('/admin-add-user', (req, res) => {
    var postData = '';
    req.on('data', function (chuck) {
        postData += chuck;
    }).on('end', function () {
        var postObj = qs.parse(postData);
        // console.log(postObj);
        var name = postObj['name'];
        var sql_string = `insert into user value(NULL, '${name}', '123456', 1);`;
        conn.query(sql_string, (err, results) => {
            // console.log(err);
            if (err) {
                res.writeHead(200, { "Content-Type": "application/json" });
                res.write(JSON.stringify({ status: false }));
                res.end();
            } else {
                res.writeHead(200, { "Content-Type": "application/json" });
                res.write(JSON.stringify({ status: true }));
                res.end();
            }
        })
    });
});

// 删除用户
app.get('/admin-delete-user', (req, res) => {
    var id = req.query['id'];
    var sql_string = `delete from user where id=${id};`;
    conn.query(sql_string, (err, results) => {
        if (err) {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(JSON.stringify({ status: false }));
            res.end();
        } else {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(JSON.stringify({ status: true }));
            res.end();
        }
    });
});

// 编辑用户
app.post('/admin-edit-user', (req, res) => {
    var id = req.query['id'];
    var postData = '';
    req.on('data', function (chuck) {
        postData += chuck;
    }).on('end', function () {
        postObj = qs.parse(postData);
        // var id = postData['id'];
        var name = postObj['name'];
        // console.log(id, name, job);
        var sql_string = `update user set name='${name}' where id=${id}`;
        conn.query(sql_string, (err, results) => {
            // console.log(err);
            if (err) {
                res.writeHead(200, { "Content-Type": "application/json" });
                res.write(JSON.stringify({ status: false }));
                res.end();
            } else {
                res.writeHead(200, { "Content-Type": "application/json" });
                res.write(JSON.stringify({ status: true }));
                res.end();
            }
        })
    });
});

// 获取公司信息
app.get('/company-info', (req, res) => {
    var sql_string = `select * from company;`;
    conn.query(sql_string, (err, results) => {
        var res_json = undefined;
        try {
            // console.log(results);
            res_json = JSON.stringify({ status: true, result: results[0] });
        } catch (err) {
            res_json = JSON.stringify({ status: false });
        } finally {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(res_json);
            res.end();
        }
    })
});

// 编辑公司信息
app.post('/edit-company-info', (req, res) => {
    var postData = '';
    req.on('data', function (chuck) {
        postData += chuck;
    }).on('end', function () {
        postObj = qs.parse(postData);
        // console.log(postObj);
        var name = postObj['name'];
        var owner = postObj['owner'];
        var listen = postObj['listen'];
        var sql_string = `update company set name='${name}', owner='${owner}', listen='${listen}';`;
        conn.query(sql_string, (err, results) => {
            // console.log(err);
            if (err) {
                res.writeHead(200, { "Content-Type": "application/json" });
                res.write(JSON.stringify({ status: false }));
                res.end();
            } else {
                res.writeHead(200, { "Content-Type": "application/json" });
                res.write(JSON.stringify({ status: true }));
                res.end();
            }
        })
    });
});

// 获取管理员信息
app.get('/admin-info', (req, res) => {
    var sql_string = 'select name from user where permission = 2';
    conn.query(sql_string, (err, results) => {
        if (err) {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(JSON.stringify({ status: false }));
            res.end();
        } else {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(JSON.stringify({ status: true, result: results[0].name }));
            res.end();
        }
    });
});

// 编辑管理员信息
app.post('/edit-admin', (req, res) => {
    var postData = '';
    var arg = url.parse(req.url).query;
    var params = qs.parse(arg);
    req.on('data', function (chuck) {
        postData += chuck;
    }).on('end', function () {
        postObj = qs.parse(postData);
        var name = postObj['name'];
        var sql_string = `update user set name='${name}' where permission=2;`;
        conn.query(sql_string, (err, results) => {
            if (err) {
                res.writeHead(200, { "Content-Type": "application/json" });
                res.write(JSON.stringify({ status: false }));
                res.end();
            } else {
                res.writeHead(200, { "Content-Type": "application/json" });
                res.write(JSON.stringify({ status: true }));
                res.end();
            }
        })
    });
});

// 获取产品列表
app.get('/products-list', (req, res) => {
    var color_filter = req.query['color-filter'];
    var size_filter = req.query['size-filter'];
    console.log(color_filter, size_filter);
    var sql_string;
    if (color_filter == '_all' && size_filter == '_all') sql_string = `select * from products;`;
    else if (color_filter == '_all' && size_filter != '_all') sql_string = `select * from products where color='${color_filter}';`;
    else if (color_filter != '_all' && size_filter == '_all') sql_string = `select * from products where size='${size_filter}';`;
    else sql_string = `select * from products where size='${size_filter}' and color='${color_filter}';`;
    conn.query(sql_string, (err, results) => {
        var res_json = undefined;
        try {
            res_json = JSON.stringify({ status: true, result: results });
        } catch (err) {
            res_json = JSON.stringify({ status: false });
        } finally {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(res_json);
            res.end();
        }
    });
});

// 检索待上架商品
app.get('/products-list-not-onshop', (req, res) => {
    var sql_string = `select * from products where is_onshop=0;`;
    // console.log(sql_string);
    conn.query(sql_string, (err, results) => {
        var res_json = undefined;
        try {
            res_json = JSON.stringify({ status: true, result: results });
        } catch (err) {
            res_json = JSON.stringify({ status: false });
        } finally {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(res_json);
            res.end();
        }
    });
});

// 检索架上商品
app.get('/products-list-onshop', (req, res) => {
    var color_filter = req.query['color-filter'];
    var size_filter = req.query['size-filter'];
    var sql_string;
    if (color_filter == '_all' && size_filter == '_all') sql_string = `select * from products where is_onshop=1;`;
    else if (color_filter == '_all' && size_filter != '_all') sql_string = `select * from products where size='${size_filter}' and is_onshop=1;`;
    else if (color_filter != '_all' && size_filter == '_all') sql_string = `select * from products where color='${color_filter}' and is_onshop=1;`;
    else sql_string = `select * from products where size='${size_filter}' and color='${color_filter}' and is_onshop=1;`;
    // console.log(sql_string);
    conn.query(sql_string, (err, results) => {
        var res_json = undefined;
        try {
            res_json = JSON.stringify({ status: true, result: results });
        } catch (err) {
            res_json = JSON.stringify({ status: false });
        } finally {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(res_json);
            res.end();
        }
    });
});

// 跳转到产品细节页面
app.get('/shop-detail', (req, res) => {
    var id = req.query['id'];
    console.log(id);
    req.session.shop = { id: id };
    // console.log(req.session.user);
    if (req.session.user['permission'] == 3)
        res.redirect('/page/shoper-shopdetail.html');
    else if (req.session.user['permission'] == 4)
        res.redirect('/page/purchaser-shopdetail.html');
    else if (req.session.user['permission'] == 1)
        res.redirect('/page/shopdetail.html');
});

// 产品细节
app.get('/get-shop-detail', (req, res) => {
    if (req.session.shop) {
        var id = req.session.shop['id'];
        var sql_string = `select * from products where id=${id};`;
        conn.query(sql_string, (err, results) => {
            // console.log(results);
            var res_json = undefined;
            try {
                res_json = JSON.stringify({ status: true, result: results[0] });
            } catch (err) {
                console.log(err);
                res_json = JSON.stringify({ status: false });
            } finally {
                res.writeHead(200, { "Content-Type": "application/json" });
                res.write(res_json);
                res.end();
            }
        });
    } else {
        res.writeHead(200, { "Content-Type": "application/json" });
        res.write(JSON.stringify({ status: false }));
        res.end();
    }
});

// 退出产品细节界面，销毁session中存储商品信息防止意外错误
app.get('/destroy-shop-session', (req, res) => {
    req.session.shop = null;
    res.writeHead(200, { "Content-Type": "application/json" });
    res.write(JSON.stringify({ status: true }));
    res.end();
});

// 添加产品
app.post('/add-product', (req, res) => {
    var postData = '';
    req.on('data', function (chuck) {
        postData += chuck;
    }).on('end', function () {
        var postObj = qs.parse(postData);
        // console.log(postObj);
        var name = postObj['name'];
        var image = '/static/img/' + postObj['image'];
        var num = postObj['num'];
        var price = postObj['price'];
        var sql_string = `insert into products value(NULL, '${name}', '${image}', ${num}, ${price});`;
        conn.query(sql_string, (err, results) => {
            if (err) {
                res.writeHead(200, { "Content-Type": "application/json" });
                res.write(JSON.stringify({ status: false }));
                res.end();
            } else {
                res.writeHead(200, { "Content-Type": "application/json" });
                res.write(JSON.stringify({ status: true }));
                res.end();
            }
        })
    });
});

// 设计产品
app.post('/devise-product', (req, res) => {
    var postData = '';
    req.on('data', function (chuck) {
        postData += chuck;
    }).on('end', function () {
        var postObj = qs.parse(postData);
        var name = postObj['name'];
        var image = '/static/img/' + postObj['image'];
        var size = postObj['size'];
        var color = postObj['color'];
        var spare_cnt = parseInt(postObj['spare_num']);
        var spare_req = [];
        var spare_num = [];
        console.log('spare_num:', spare_num)
        for (var idx = 1; idx <= spare_cnt; idx += 1) {
            spare_req.push(parseInt(postObj['spare_' + idx]));
            spare_num.push(parseInt(postObj['spare_' + idx + '_num']));
        }
        spare_req = new Array(spare_req);
        spare_num = new Array(spare_num);
        // var spare_req = new Array([
        //     postObj['spare_1'],
        //     postObj['spare_2'],
        //     postObj['spare_3'],
        //     postObj['spare_4'],
        // ]);
        // var spare_num = new Array([
        //     postObj['spare_1_num'],
        //     postObj['spare_2_num'],
        //     postObj['spare_3_num'],
        //     postObj['spare_4_num'],
        // ]);
        var sql_string = `insert into products value(NULL, '${name}', '${image}', 0, '${size}', '${color}', 0);`;
        conn.query(sql_string, (err, results) => {
            if (err) {
                console.log(1, err);
                res.writeHead(200, { "Content-Type": "application/json" });
                res.write(JSON.stringify({ status: false }));
                res.end();
            } else {
                console.log(results);
                var id = results['insertId'];
                var sql_string = `insert into product_with_spare values `;
                spare_req.forEach((val, _idx, _arr) => {
                    val.forEach((sub_val, sub_idx, _sub_arr) => {
                        console.log(sub_val);
                        if (sub_val != -1) {
                            sql_string += `(NULL, ${sub_val}, ${id}, ${spare_num[0][sub_idx]}),`;
                        }
                    })
                });
                sql_string = sql_string.substring(0, sql_string.length - 1) + `;`;
                conn.query(sql_string, (err, _results) => {
                    if (err) {
                        // console.log(2, err);
                        res.writeHead(200, { "Content-Type": "application/json" });
                        res.write(JSON.stringify({ status: false }));
                        res.end();
                    } else {
                        res.writeHead(200, { "Content-Type": "application/json" });
                        res.write(JSON.stringify({ status: true }));
                        res.end();
                    }
                })
            }
        });
    });
});

// 产品上架
app.post('/on-shop', (req, res) => {
    var id = req.query['id'];
    var postData = '';
    req.on('data', function (chuck) {
        postData += chuck;
    }).on('end', function () {
        var postObj = qs.parse(postData);
        var price = postObj['price'];
        var sql_string = `update products set is_onshop=1, price=${price} where id=${id}`;
        conn.query(sql_string, (err, results) => {
            if (err) {
                res.writeHead(200, { "Content-Type": "application/json" });
                res.write(JSON.stringify({ status: false }));
                res.end();
            } else {
                res.writeHead(200, { "Content-Type": "application/json" });
                res.write(JSON.stringify({ status: true }));
                res.end();
            }
        })
    });
});

// 产品下架
app.get('/off-shop', (req, res) => {
    var id = req.query['id'];
    var sql_string = `update products set is_onshop=0, price=0 where id=${id}`;
    conn.query(sql_string, (err, results) => {
        if (err) {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(JSON.stringify({ status: false }));
            res.end();
        } else {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(JSON.stringify({ status: true }));
            res.end();
        }
    });
});

// 删除产品
app.get('/delete-product', (req, res) => {
    var id = req.query['id'];
    var sql_string = `delete from products where id=${id};`;
    conn.query(sql_string, (err, results) => {
        if (err) {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(JSON.stringify({ status: false }));
            res.end();
        } else {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(JSON.stringify({ status: true }));
            res.end();
        }
    });
});

// 编辑产品
app.post('edit-product', (req, res) => {
    var id = req.query['id'];
    var postData = '';
    req.on('data', function (chuck) {
        postData += chuck;
    }).on('end', function () {
        postObj = qs.parse(postData);
        var name = postObj['name'];
        var image = '/static/img' + postObj['image'];
        var num = postObj['num'];
        var sql_string = `update products set name='${name}', image='${image}', num=${num} where id=${id}`;
        conn.query(sql_string, (err, results) => {
            if (err) {
                res.writeHead(200, { "Content-Type": "application/json" });
                res.write(JSON.stringify({ status: false }));
                res.end();
            } else {
                res.writeHead(200, { "Content-Type": "application/json" });
                res.write(JSON.stringify({ status: true }));
                res.end();
            }
        })
    });
});

// 获取零件列表
app.get('/spares-list', (req, res) => {
    var sql_string = `select t1.id as id, t1.name as name, t1.num as num, t2.warning_num as warning_num from spares t1 inner join spare_warnings t2 on t1.id = t2.spare_id;`;
    conn.query(sql_string, (err, results) => {
        var res_json = undefined;
        try {
            // console.log(results);
            res_json = JSON.stringify({ status: true, result: results });
        } catch (err) {
            res_json = JSON.stringify({ status: false });
        } finally {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(res_json);
            res.end();
        }
    })
});

// 零件细节
app.get('/shop-detail', (req, res) => {
    var id = req.query['id'];
    var sql_string = `select * from spares where id=${id};`;
    conn.query(sql_string, (err, results) => {
        var res_json = undefined;
        try {
            res_json = JSON.stringify({ status: true, result: results[0] });
        } catch (err) {
            res_json = JSON.stringify({ status: false });
        } finally {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(res_json);
            res.end();
        }
    });
});

// 跳转到产品细节页面
app.get('/spare-detail', (req, res) => {
    var id = req.query['id'];
    var permission = req.session.user['permission'];
    req.session.spare = { id: id };
    if (permission == 3)
        res.redirect('/page/shoper-sparedetail.html');
    else
        res.redirect('/page/purchaser-sparedetail.html');
});

// 零件细节
app.get('/get-spare-detail', (req, res) => {
    if (req.session.spare) {
        var id = req.session.spare['id'];
        var sql_string = `select * from spares where id=${id};`;
        conn.query(sql_string, (err, results) => {
            var res_json = undefined;
            try {
                res_json = JSON.stringify({ status: true, result: results[0] });
            } catch (err) {
                res_json = JSON.stringify({ status: false });
            } finally {
                res.writeHead(200, { "Content-Type": "application/json" });
                res.write(res_json);
                res.end();
            }
        });
    } else {
        res.writeHead(200, { "Content-Type": "application/json" });
        res.write(JSON.stringify({ status: false }));
        res.end();
    }
});

// 退出产品细节界面，销毁session中存储商品信息防止意外错误
app.get('/destroy-spare-session', (req, res) => {
    req.session.spare = null;
    res.writeHead(200, { "Content-Type": "application/json" });
    res.write(JSON.stringify({ status: true }));
    res.end();
});

// 零件出库
app.post('/output-spare', (req, res) => {
    var id = req.session.spare['id'];
    var postData = '';
    req.on('data', function (chuck) {
        postData += chuck;
    }).on('end', function () {
        postObj = qs.parse(postData);
        var num = postObj['num'];
        var sql_string = `update spares set num=${num} where id=${id}`;
        conn.query(sql_string, (err, results) => {
            if (err) {
                res.writeHead(200, { "Content-Type": "application/json" });
                res.write(JSON.stringify({ status: false }));
                res.end();
            } else {
                res.writeHead(200, { "Content-Type": "application/json" });
                res.write(JSON.stringify({ status: true }));
                res.end();
            }
        })
    });
});

// 零件入库
app.post('/input-spare', (req, res) => {
    var id = req.query['id'];
    var postData = '';
    req.on('data', function (chuck) {
        postData += chuck;
    }).on('end', function () {
        postObj = qs.parse(postData);
        var num = postObj['num'];
        var pay = postObj['pay'];
        // var sql_string = `update spares set num=${num} where id=${id}`;
        var sql_string = `select num from spares where id=${id}`;
        var sql_string_3 = `select fund from baseinfo limit 1`;
        conn.query(sql_string, (err, results) => {
            if (err) {
                res.writeHead(200, { "Content-Type": "application/json" });
                res.write(JSON.stringify({ status: false }));
                res.end();
            } else {
                var org_num = results[0]['num'];
                // console.log(parseInt(num), parseInt(org_num));
                var sql_string_1 = `update spares set num=${parseInt(num) + parseInt(org_num)} where id=${id}`;
                var sql_string_2 = `insert into fund_change value(NULL, -${pay}, '采购零件')`;
                conn.query(sql_string_1, (err, results) => {
                    if (err) {
                        console.log(err);
                        res.writeHead(200, { "Content-Type": "application/json" });
                        res.write(JSON.stringify({ status: false }));
                        res.end();
                    } else {
                        res.writeHead(200, { "Content-Type": "application/json" });
                        res.write(JSON.stringify({ status: true }));
                        res.end();
                    }
                });
                conn.query(sql_string_2, (err, results) => { });
            }
        });
        conn.query(sql_string_3, (err, results) => {
            if (err) {
                console.log('资金修改错误');
            } else {
                var sql_string_4 = `update baseinfo set fund=${results[0]['fund'] - pay}`;
                conn.query(sql_string_4, (err, results) => { });
            }
        });
    });
});

// 添加零件
app.post('/add-spare', (req, res) => {
    var postData = '';
    req.on('data', function (chuck) {
        postData += chuck;
    }).on('end', function () {
        var postObj = qs.parse(postData);
        var name = postObj['name'];
        var image = '/static/img/' + postObj['image'];
        var num = postObj['num'];
        var warning_num = 0;
        var sql_string = `insert into spares value(NULL, '${name}', '${image}', ${num});`;
        conn.query(sql_string, (err, results) => {
            if (err) {
                res.writeHead(200, { "Content-Type": "application/json" });
                res.write(JSON.stringify({ status: false }));
                res.end();
            } else {
                // console.log('insert id: ', results['insertId']);
                var sql_string = `insert into spare_warnings value(NULL, ${results['insertId']}, ${warning_num});`
                conn.query(sql_string, (err, results) => {
                    if (err) {
                        res.writeHead(200, { "Content-Type": "application/json" });
                        res.write(JSON.stringify({ status: false }));
                        res.end();
                    } else {
                        res.writeHead(200, { "Content-Type": "application/json" });
                        res.write(JSON.stringify({ status: true }));
                        res.end();
                    }
                });
            }
        })
    });
});

// 删除零件
app.get('/delete-spare', (req, res) => {
    var id = req.query['id'];
    var sql_string = `delete from spares where id=${id};`;
    conn.query(sql_string, (err, results) => {
        if (err) {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(JSON.stringify({ status: false }));
            res.end();
        } else {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(JSON.stringify({ status: true }));
            res.end();
        }
    });
});

// 编辑零件
app.post('/edit-spare', (req, res) => {
    var id = req.query['id'];
    var postData = '';
    req.on('data', function (chuck) {
        postData += chuck;
    }).on('end', function () {
        postObj = qs.parse(postData);
        var name = postObj['name'];
        var image = '/static/img' + postObj['image'];
        var num = postObj['num'];
        var sql_string = `update spares set name='${name}', image='${image}', num=${num} where id=${id}`;
        conn.query(sql_string, (err, results) => {
            if (err) {
                res.writeHead(200, { "Content-Type": "application/json" });
                res.write(JSON.stringify({ status: false }));
                res.end();
            } else {
                res.writeHead(200, { "Content-Type": "application/json" });
                res.write(JSON.stringify({ status: true }));
                res.end();
            }
        })
    });
});

app.get('/set-spare-warning-number', (req, res) => {
    var id = req.query['id'];
    var set_num = req.query['set_num'];
    var sql_string = `update spare_warnings set warning_num=${set_num} where spare_id=${id}`;
    conn.query(sql_string, (err, results) => {
        if (err) {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(JSON.stringify({ status: false }));
            res.end();
        } else {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(JSON.stringify({ status: true }));
            res.end();
        }
    });
});

// 将商品添加到购物车
app.get('/add-to-cart', (req, res) => {
    var user_id = req.session.user['id'];
    var shop_id = req.session.shop['id'];
    var sql_string = `insert into shopcart value (NULL, ${user_id}, ${shop_id}, 1);`;
    conn.query(sql_string, (err, results) => {
        if (err) {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(JSON.stringify({ status: false }));
            res.end();
        } else {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(JSON.stringify({ status: true }));
            res.end();
        }
    });
});

// 遍历当前用户的购物车
app.get('/get-user-cart', (req, res) => {
    var user_id = req.session.user['id'];
    var sql_string = `select t2.id as id, t3.name as product_name, t3.price as price, count(t3.name) as num from user t1 inner join shopcart t2 on t1.id=t2.user_id inner join products t3 on t2.product_id=t3.id where user_id=${user_id} GROUP BY product_name, id, price;`;
    conn.query(sql_string, (err, results) => {
        if (err) {
            console.log(err);
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(JSON.stringify({ status: false }));
            res.end();
        } else {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(JSON.stringify({ status: true, result: results }));
            res.end();
        }
    });
});

// 从购物车删除
app.get('/delete-from-cart', (req, res) => {
    var id = req.query['id'];
    var sql_string = `delete from shopcart where id=${id}`;
    conn.query(sql_string, (err, results) => {
        if (err) {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(JSON.stringify({ status: false }));
            res.end();
        } else {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(JSON.stringify({ status: true }));
            res.end();
        }
    });
});

// 付款并将购物车转化为订单
app.post('/switch-cart-to-order', (req, res) => {
    var user_id = req.session.user['id'];
    var postData = '';
    req.on('data', function (chuck) {
        postData += chuck;
    }).on('end', function () {
        var postObj = qs.parse(postData);
        phone = postObj['phone'];
        location = postObj['location'];
        var sql_string = `select t3.id as id, t2.id as cart_id, t3.name as product_name, t3.price as price, count(t3.name) as num from user t1 inner join shopcart t2 on t1.id=t2.user_id inner join products t3 on t2.product_id=t3.id where user_id=${user_id} GROUP BY product_name, id, price, cart_id;`;
        conn.query(sql_string, (err, results) => {
            if (err) {
                console.log(1, err);
                res.writeHead(200, { "Content-Type": "application/json" });
                res.write(JSON.stringify({ status: false }));
                res.end();
            } else {
                var products_list = new Array(results[0]);
                var cart_id = products_list[0]['cart_id'];
                sql_string = `delete from shopcart where id=${cart_id}`;
                conn.query(sql_string, (err, results) => {
                    if (err) {
                        console.log(0, err);
                    }
                });
                sql_string = `insert into orders value(NULL, ${user_id}, NULL, '${phone}', '${location}', 0, NULL, NULL, 0)`;
                conn.query(sql_string, (err, results) => {
                    var total_count = 0;
                    if (err) {
                        console.log(2, err);
                        res.writeHead(200, { "Content-Type": "application/json" });
                        res.write(JSON.stringify({ status: false }));
                        res.end();
                    } else {
                        var order_id = results['insertId'];
                        products_list.forEach((val, _idx, _arr) => {
                            sql_string = `insert into orders_with_product value(NULL, ${order_id}, ${val['id']}, ${val['num']})`;
                            conn.query(sql_string, (err, results) => { });
                            total_count += parseInt(val['num']) * parseInt(val['price']);
                        })
                        sql_string = `update orders set money=${total_count} where id=${order_id}`;
                        conn.query(sql_string, (err, results) => {
                            if (err) {
                                console.log(3, err);
                                res.writeHead(200, { "Content-Type": "application/json" });
                                res.write(JSON.stringify({ status: false }));
                                res.end();
                            } else {
                                res.writeHead(200, { "Content-Type": "application/json" });
                                res.write(JSON.stringify({ status: true }));
                                res.end();
                            }
                        });
                    }
                });
            }
        })
    });
});

app.get('/user-self-info', (req, res) => {
    var user_id = req.session.user['id'];
    var sql_string = `select name from user where id=${user_id}`;
    conn.query(sql_string, (err, results) => {
        if (err) {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(JSON.stringify({ status: false }));
            res.end();
        } else {
            // console.log(results);
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(JSON.stringify({ status: true, result: results[0] }));
            res.end();
        }
    });
})

app.post('/user-edit-self', (req, res) => {
    var user_id = req.session.user['id'];
    var postData = '';
    req.on('data', function (chuck) {
        postData += chuck;
    }).on('end', function () {
        postObj = qs.parse(postData);
        var name = postObj['name'];
        var sql_string = `update user set name='${name}' where id=${user_id}`;
        conn.query(sql_string, (err, results) => {
            if (err) {
                res.writeHead(200, { "Content-Type": "application/json" });
                res.write(JSON.stringify({ status: false }));
                res.end();
            } else {
                res.writeHead(200, { "Content-Type": "application/json" });
                res.write(JSON.stringify({ status: true }));
                res.end();
            }
        })
    });
})

// 遍历当前用户的订单
app.get('/get-user-self-orders', (req, res) => {
    var user_id = req.session.user['id'];
    var sql_string = `select t1.name as username, t2.id as id, t4.name as name, t4.price as price from user t1 inner join orders t2 on t1.id=t2.user_id inner join orders_with_product t3 on t2.id=t3.order_id inner join products t4 on t3.product_id=t4.id where user_id=${user_id};`;
    conn.query(sql_string, (err, results) => {
        if (err) {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(JSON.stringify({ status: false }));
            res.end();
        } else {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(JSON.stringify({ status: true, result: results }));
            res.end();
        }
    });
});

// 获取等待处理的订单
app.get('/get-waiting-orders', (req, res) => {
    var sql_string = `select * from orders where is_export=0`;
    conn.query(sql_string, (err, results) => {
        if (err) {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(JSON.stringify({ status: false }));
            res.end();
        } else {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(JSON.stringify({ status: true, result: results }));
            res.end();
        }
    });
});

// 获取历史订单
app.get('/get-history-orders', (req, res) => {
    var sql_string = `select * from orders where is_export=1`;
    conn.query(sql_string, (err, results) => {
        if (err) {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(JSON.stringify({ status: false }));
            res.end();
        } else {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(JSON.stringify({ status: true, result: results }));
            res.end();
        }
    });
});

// 获取指定订单信息
app.get('/get-order-info', (req, res) => {
    var order_id = req.query['id'];
    var sql_string = `select * from orders where id=${order_id}`;
    conn.query(sql_string, (err, results) => {
        if (err) {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(JSON.stringify({ status: false }));
            res.end();
        } else {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(JSON.stringify({ status: true, result: results[0] }));
            res.end();
        }
    });
});

// 订单发货
// app.post('')

// 获取订单下产品列表
app.get('/get-order-products', (req, res) => {
    var id = req.query['id'];
    sql_string = `select t1.id as order_id, t3.name as name, t2.num as num, t3.id as product_id, t3.price as price from orders t1 inner join orders_with_product t2 on t1.id = t2.order_id inner join products t3 on t3.id = t2.product_id where order_id=${id}`;
    conn.query(sql_string, (err, results) => {
        if (err) {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(JSON.stringify({ status: false }));
            res.end();
        } else {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(JSON.stringify({ status: true, result: results }));
            res.end();
        }
    });
});

app.get('/get-user-info', (req, res) => {
    // console.log(req.query);
    var user_id = req.query['id'];
    var sql_string = `select t1.name as username, t2.id as id, t4.name as name, t4.price as price from user t1 inner join orders t2 on t1.id=t2.user_id inner join orders_with_product t3 on t2.id=t3.order_id inner join products t4 on t3.product_id=t4.id where user_id=${user_id};`;
    conn.query(sql_string, (err, results) => {
        if (err) {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(JSON.stringify({ status: false }));
            res.end();
        } else {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(JSON.stringify({ status: true, result: results }));
            res.end();
        }
    });
});

app.get('/shop-search-list', (req, res) => {
    var sql_string = `select id, name, size, color from products`;
    conn.query(sql_string, (err, results) => {
        if (err) {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(JSON.stringify({ status: false }));
            res.end();
        } else {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(JSON.stringify({ status: true, result: results }));
            res.end();
        }
    });
});

app.get('/fund-info', (req, res) => {
    var sql_string = `select fund from baseinfo;`;
    var res_dict = {};
    conn.query(sql_string, (err, results) => {
        if (err) {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(JSON.stringify({ status: false }));
            res.end();
        } else {
            // console.log(results[0])
            res_dict['fund'] = results[0]['fund'];
            var sql_string = `select * from fund_change;`;
            conn.query(sql_string, (err, results) => {
                if (err) {
                    res.writeHead(200, { "Content-Type": "application/json" });
                    res.write(JSON.stringify({ status: false }));
                    res.end();
                } else {
                    res_dict['fund_detail'] = results;
                    res_dict = JSON.stringify(res_dict);
                    console.log(res_dict);
                    res.writeHead(200, { "Content-Type": "application/json" });
                    res.write(JSON.stringify({ status: true, result: res_dict }));
                    res.end();
                }
            })
        }
    });
});

// 获取物流列表
app.get('/logistic-list', (req, res) => {
    var sql_string = `select * from logistics;`;
    conn.query(sql_string, (err, results) => {
        var res_json = undefined;
        try {
            res_json = JSON.stringify({ status: true, result: results });
        } catch (err) {
            res_json = JSON.stringify({ status: false });
        } finally {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(res_json);
            res.end();
        }
    });
});

// 添加物流
app.post('/add-logistic', (req, res) => {
    var postData = '';
    req.on('data', function (chuck) {
        postData += chuck;
    }).on('end', function () {
        var postObj = qs.parse(postData);
        var name = postObj['name'];
        var sql_string = `insert into logistics value(NULL, '${name}');`;
        conn.query(sql_string, (err, results) => {
            if (err) {
                res.writeHead(200, { "Content-Type": "application/json" });
                res.write(JSON.stringify({ status: false }));
                res.end();
            } else {
                res.writeHead(200, { "Content-Type": "application/json" });
                res.write(JSON.stringify({ status: true }));
                res.end();
            }
        })
    });
});

// 编辑物流
app.post('/edit-logistic', (req, res) => {
    var id = req.query['id'];
    var postData = '';
    req.on('data', function (chuck) {
        postData += chuck;
    }).on('end', function () {
        postObj = qs.parse(postData);
        var name = postObj['name'];
        var sql_string = `update logisticss set name='${name}' where id=${id}`;
        conn.query(sql_string, (err, results) => {
            if (err) {
                res.writeHead(200, { "Content-Type": "application/json" });
                res.write(JSON.stringify({ status: false }));
                res.end();
            } else {
                res.writeHead(200, { "Content-Type": "application/json" });
                res.write(JSON.stringify({ status: true }));
                res.end();
            }
        })
    });
});

// 删除物流
app.get('/delete-logistic', (req, res) => {
    var id = req.query['id'];
    var sql_string = `delete from logistics where id=${id};`;
    conn.query(sql_string, (err, results) => {
        if (err) {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(JSON.stringify({ status: false }));
            res.end();
        } else {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(JSON.stringify({ status: true }));
            res.end();
        }
    });
});

// 订单发货
app.post('/order-export', (req, res) => {
    var id = req.query['id'];
    var shoper_id = req.session.user['id'];
    var postData = '';
    req.on('data', function (chuck) {
        postData += chuck;
    }).on('end', function () {
        postObj = qs.parse(postData);
        var logistic_id = postObj['logistic'];
        var money = postObj['money'];
        // var sql_string = `update orders set logistic_id=${logistic_id}, is_export=1, export_parter_id=${shoper_id} where id=${id}`;
        var sql_string = `select t1.id as order_id, t3.name as name, t2.num as num, t3.id as product_id, t3.price as price from orders t1 inner join orders_with_product t2 on t1.id = t2.order_id inner join products t3 on t3.id = t2.product_id where order_id=${id}`;
        conn.query(sql_string, (err, results) => {
            if (err) {
                throw 0;
            } else {
                var result_list = new Array(results[0]);
                result_list.forEach((val, _idx, _arr) => {
                    var product_id = val['product_id'];
                    var sql_string = `select t1.spare_num as spare_num, t2.num as all_num, t2.id as spareid from product_with_spare t1 inner join spares t2 on t1.spare_id=t2.id where product_id=${product_id}`;
                    conn.query(sql_string, (err, results) => {
                        if (err) console.log(err);
                        var sub_res = results[0];
                        if (sub_res['spare_num'] > sub_res['all_num']) {
                            res.writeHead(200, { "Content-Type": "application/json" });
                            res.write(JSON.stringify({ status: false, status_code: 1 }));
                            res.end();
                        } else {
                            var sql_string = `update spares set num=${parseInt(sub_res['all_num']) - parseInt(sub_res['spare_num'])} where id=${sub_res['spareid']}`;
                            conn.query(sql_string, (err, results) => { console.log(err) });
                            var sql_string = `update orders set logistic_id=${logistic_id}, is_export=1, export_parter_id=${shoper_id} where id=${id}`;
                            conn.query(sql_string, (err, results) => {
                                if (err) throw 0;
                                else {
                                    res.writeHead(200, { "Content-Type": "application/json" });
                                    res.write(JSON.stringify({ status: true }));
                                    res.end();
                                }
                            });
                        }
                    })
                });
                var sql_string = `insert into fund_change value (NULL, ${money}, '用户结算')`;
                conn.query(sql_string, (err, results) => { });
                var sql_string = `select fund from baseinfo`;
                conn.query(sql_string, (err, results) => {
                    if (!err) {
                        var fund = results[0]['fund'];
                        var sql_string = `update baseinfo set fund=${parseInt(fund) + parseInt(money)}`;
                        conn.query(sql_string, (err, results) => { });
                    } else {
                        console.log(err);
                    }
                });
            }
        })
    });
});

// 获取分类
app.get('/get-shop-classfier', (req, res) => {
    var sql_string = 'select color, size from products where is_onshop=1;';
    conn.query(sql_string, (err, results) => {
        if (err) {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(JSON.stringify({ status: false }));
            res.end();
        } else {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(JSON.stringify({ status: true, result: results }));
            res.end();
        }
    });
});

app.get('/get-user-self-wait-received-orders', (req, res) => {
    var user_id = req.session.user['id'];
    var sql_string = `select t1.name as username, t2.id as id, t4.name as name, t4.price as price from user t1 inner join orders t2 on t1.id=t2.user_id inner join orders_with_product t3 on t2.id=t3.order_id inner join products t4 on t3.product_id=t4.id where user_id=${user_id} and is_received=0;`;
    conn.query(sql_string, (err, results) => {
        if (err) {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(JSON.stringify({ status: false }));
            res.end();
        } else {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(JSON.stringify({ status: true, result: results }));
            res.end();
        }
    });
});

app.get('/received', (req, res) => {
    var id = req.query['id'];
    var sql_string = `update orders set is_received=1 where id=${id}`;
    conn.query(sql_string, (err, results) => {
        if (err) {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(JSON.stringify({ status: false }));
            res.end();
        } else {
            res.writeHead(200, { "Content-Type": "application/json" });
            res.write(JSON.stringify({ status: true }));
            res.end();
        }
    });
})

// 无效路由处理
app.get('*', (req, res) => {
    res.sendFile('/page/404.html', { root: root_path });
})

app.listen(8000, '0.0.0.0', () => {
    console.log('打开 http://localhost:8000/ 以继续');
});